package com.bestv.search.spider.douban.dao.hibernate;

import com.bestv.search.spider.dao.hibernate.GenericDaoHibernate;
import com.bestv.search.spider.douban.dao.DouBanVideoBaseInfoDao;
import com.bestv.search.spider.douban.model.DouBanVideoBaseInfo;
import org.hibernate.*;
import org.hibernate.transform.Transformers;
import org.springframework.orm.hibernate3.HibernateCallback;

import java.sql.SQLException;
import java.util.Date;
import java.util.List;

public class DouBanVideoBaseInfoDaoHibernate extends GenericDaoHibernate<DouBanVideoBaseInfo, Long> implements
        DouBanVideoBaseInfoDao {

    public DouBanVideoBaseInfoDaoHibernate() {
        super(DouBanVideoBaseInfo.class);
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<DouBanVideoBaseInfo> getUnSyncedInfo() {
        String sql = "select base.id, base.name, base.source, base.updateTime, youku.category as subtype"
                + " from videoBaseInfo as base inner join youKuVideoBaseInfo as youku on"
                + " base.id = youku.id left join douBanVideoBaseInfo as douban on"
                + " base.id = douban.id where  douban.subject_id is null and douban.isValid is null";

        List<DouBanVideoBaseInfo> result = null;
        Session session = null;
        try {
            session = getSession();
            Query query = session.createSQLQuery(sql).addScalar("id", Hibernate.STRING)
                    .addScalar("name", Hibernate.STRING).addScalar("source", Hibernate.STRING)
                    .addScalar("updateTime", Hibernate.DATE).addScalar("subtype", Hibernate.STRING)
                    .setResultTransformer(Transformers.aliasToBean(DouBanVideoBaseInfo.class));

            result = query.list();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (session != null)
                releaseSession(session);
        }

        return result;
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<DouBanVideoBaseInfo> getValidInfos() {
        String hql = "from DouBanVideoBaseInfo where isValid = 1";
        return getHibernateTemplate().find(hql);
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<DouBanVideoBaseInfo> getValidInfoBySyncReviewsTime(Date syncTime) {
        String hql = "from DouBanVideoBaseInfo where isValid = 1 and (reviewsSyncTime < ? or reviewsSyncTime is null)";
        return getHibernateTemplate().find(hql, new Object[]{syncTime});
    }

    @Override
    public boolean existBaseInfo(DouBanVideoBaseInfo info) {
        String hql = "from VideoBaseInfo where id=? and source =?";
        List<?> list = getHibernateTemplate().find(hql, new Object[]{info.getId(), info.getSource()});
        return list == null || list.size() == 0 ? false : true;
    }

    @Override
    public boolean existDouBanInfo(DouBanVideoBaseInfo info) {
        String sql = "select * from douBanVideoBaseInfo where id = ? ";
        Session session = null;
        try {
            session = getSession();
            Query query = session.createSQLQuery(sql);
            query.setString(0, info.getId());

            List<?> list = query.list();
            return list == null || list.size() == 0 ? false : true;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (session != null)
                releaseSession(session);
        }

        return false;
    }

    @Override
    public void saveDouBanVideoInfo(final DouBanVideoBaseInfo info) {
        getHibernateTemplate().execute(new HibernateCallback() {
            String sql = "insert into douBanVideoBaseInfo(id, original_title, aliasName, subtype,"
                    + " year, subject_id, score, isValid) values(?, ?, ?, ?, ?, ?, ?, ?)";

            public Object doInHibernate(Session session) throws HibernateException, SQLException {
                SQLQuery sqlQuery = session.createSQLQuery(sql);
                sqlQuery.setString(0, info.getId());
                sqlQuery.setString(1, info.getOriginal_title());
                sqlQuery.setString(2, info.getAliasName());
                sqlQuery.setString(3, info.getSubtype());
                sqlQuery.setInteger(4, info.getYear());
                sqlQuery.setString(5, info.getSubject_id());
                sqlQuery.setDouble(6, info.getScore() == null ? 0 : info.getScore());
                sqlQuery.setInteger(7, info.getIsValid());
                sqlQuery.executeUpdate();
                return null;
            }
        });
    }

    @Override
    public void updateDouBanVideoInfo(final DouBanVideoBaseInfo info) {
        getHibernateTemplate().execute(new HibernateCallback() {
            String sql = "update douBanVideoBaseInfo set original_title = ?, aliasName = ?, subtype = ?,"
                    + " year = ?, subject_id = ?, score = ?, isValid = ? where id =?";

            public Object doInHibernate(Session session) throws HibernateException, SQLException {
                SQLQuery sqlQuery = session.createSQLQuery(sql);
                sqlQuery.setString(0, info.getOriginal_title());
                sqlQuery.setString(1, info.getAliasName());
                sqlQuery.setString(2, info.getSubtype());
                sqlQuery.setInteger(3, info.getYear());
                sqlQuery.setString(4, info.getSubject_id());
                sqlQuery.setDouble(5, info.getScore() == null ? 0 : info.getScore());
                sqlQuery.setInteger(6, info.getIsValid());
                sqlQuery.setString(7, info.getId());
                sqlQuery.executeUpdate();
                return null;
            }
        });
    }
}
